package si.assign1.manager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import si.assign1.model.Category;
import si.assign1.model.Product;
import si.assign1.model.ProductType;
import si.assign1.model.ProductTypeAttribute;
import si.assign1.model.ProductTypeAttributeValue;

public class ProductSectionCreator {
	private EntityManager em;
	
	/*
	 * typical order of object creation:
	 *  1. ProductType
	 *  2. ProductTypeAttribute dependent on ProductType
	 *  3. Product dependent on ProductType, mutual-dependent on Category
	 *  4. ProductTypeAttributeValue dependent on ProductTypeAttribute and Product
	 *  5. Category dependent on ProductType, mutual-dependent on Product
	 */
	
	public ProductSectionCreator(EntityManager em) {
		this.em = em;
	}

	/**
	 * 
	 * @param name name of the product type to create
	 * @return the product type that's been created
	 */
	public ProductType createProductType(String name) {
		ProductType pt = new ProductType();
		pt.setName(name);
		
		em.persist(pt);
		
		return pt;
	}
	
	/**
	 * 
	 * @param name name of the attribute to create
	 * @param isRequired a char value indicating if the attribute is required
	 * @param productType product type that this attribute belongs to 
	 * @return the attribute that's been created
	 */
	public ProductTypeAttribute createProductTypeAttribute(
			String name, char isRequired, ProductType productType) {
		ProductTypeAttribute pta = new ProductTypeAttribute();
		pta.setName(name);
		pta.setIsRequired(isRequired);
		
		productType.addProductTypeAttribute(pta);
		
		em.persist(pta);
		
		return pta;
	}


	/**
	 * 
	 * @param name name of the product to create
	 * @param sku stock keeping unit of the product
	 * @param description description of the product
	 * @param purchasePrice purchase price
	 * @param salePrice sale price
	 * @param productType type of the product
	 * @return the product that's been created
	 */
	public Product createProduct(String name, String sku, 
			String description, double purchasePrice, double salePrice,
			ProductType productType) {
		Product p = new Product();
		p.setName(name);
		p.setSku(sku);
		p.setDescription(description);
		p.setPurchasePrice(purchasePrice);
		p.setSalePrice(salePrice);
		
		productType.addProduct(p);
		
		Set<ProductTypeAttribute> ptas = productType.getProductTypeAttributes();
		for (ProductTypeAttribute pta : ptas) {
			/**
			 * each product must have a separate product type attribute value instance
			 */
			ProductTypeAttributeValue ptav = new ProductTypeAttributeValue();
			p.addProductTypeAttributeValue(ptav);
			pta.addProductTypeAttributeValue(ptav);
			em.persist(ptav);
		}
		
		em.persist(p);
		
		return p;
	}
	
	/**
	 * 
	 * @param value value of the attribute to create
	 * @param product product that owns this attribute
	 * @param productTypeAttribute attribute that this value represents
	 * @return the object of attribute that's been created
	 */
	public ProductTypeAttributeValue createProductTypeAttributeValue(
			String value,
			Product product,
			ProductTypeAttribute productTypeAttribute) {
		ProductTypeAttributeValue ptav = new ProductTypeAttributeValue();
		
		ptav.setValue(value);
		product.addProductTypeAttributeValue(ptav);
		productTypeAttribute.addProductTypeAttributeValue(ptav);
		
		em.persist(ptav);
		
		return ptav;
	}
	
	/*
	 * this is another way (and easier way) of setting product type attribute value
	 */
	/**
	 * 
	 * @param product product that has already have types and attributes created and linked to it
	 * @param attributeName name of the attribute to set
	 * @param value the value to assign to the attribute
	 */
	public void setProductTypeAttributeValueForProduct(
			Product product, String attributeName, String value) {
		
		ProductType pt = product.getProductType();
		Set<ProductTypeAttribute> ptas = pt.getProductTypeAttributes();
		for (ProductTypeAttribute pta : ptas) {
			String name = pta.getName();
			if (name.equals(attributeName)) {	/* the attribute has the specified name */
				Set<ProductTypeAttributeValue> ptavs 
					= pta.getProductTypeAttributeValues();
				/**
				 * whether the value object already exists
				 */
				for (ProductTypeAttributeValue ptav: ptavs) {
					Product p = ptav.getProduct();
					if (p.equalsById(product)) {	/* it is on the specified product */
						ptav.setValue(value);
						return;
					}
				}
				ProductTypeAttributeValue newPtav = new ProductTypeAttributeValue();
				newPtav.setValue(value);
				product.addProductTypeAttributeValue(newPtav);
				pta.addProductTypeAttributeValue(newPtav);
				
				em.persist(newPtav);
				return;
			}
		}
		/* 
		 * at this stage, the corresponding product is not found
		 */
	}
	
	/**
	 * 
	 * @param name name of the category
	 * @param parent parent of the category, null for root
	 * @param productType product-type that this category maps to 
	 * @return the category that's been created or assigned to the product
	 */
	public Category assignCategoryToProduct(String name, Category parent, Product product) {
		ProductType pt = product.getProductType();
		
		Query q = em.createNamedQuery("getCategoriesByNameAndProductType");
		q.setParameter("name", name);
		q.setParameter("productType", pt);
		try { 
			Category existingCategory = (Category)q.getSingleResult();
			existingCategory.addProduct(product);
			return existingCategory;
		} catch (NoResultException e) {
		}
		
		Category c = new Category();
		c.setName(name);
		if (parent == null) {
			q = em.createNamedQuery("getCategoriesByName");
	        q.setParameter("name", "Root");
	        Category root = (Category)q.getSingleResult();
	        c.setParent(root);
		} else {
			c.setParent(parent);
		}
		c.addProduct(product);
		pt.addCategory(c);
		
		em.persist(c);
		
		return c;
	}
	
	public void CleanUp() {
		Connection conn = null; 
		try {
			conn = DriverManager.getConnection("jdbc:db2://204.90.115.30:5025/DALLAS9:retrieveMessagesFromServerOnGetMessage=true;emulateParameterMetaDataForZCalls=1;", USER, PWD);
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			
			int rows1 = stmt.executeUpdate("DELETE FROM PRODUCTS");
			int rows2 = stmt.executeUpdate("DELETE FROM PRODUCT_TYPES WHERE ID<>1");	/* not to delete the root product type */
			int rows3 = stmt.executeUpdate("DELETE FROM PRODUCT_TYPE_ATTRIBUTES");
			int rows4 = stmt.executeUpdate("DELETE FROM PRODUCT_TYPE_ATTRIBUTE_VALUES");
			int rows5 = stmt.executeUpdate("DELETE FROM CATEGORIES WHERE name <> 'Root'");
			int rows6 = stmt.executeUpdate("DELETE FROM PRODUCT_CATEGORIES");
			
			conn.commit();
			System.out.printf("Successfully deleted %d rows for PRODUCTS table\n", rows1);
			System.out.printf("Successfully deleted %d rows for PRODUCT_TYPES table\n", rows2);
			System.out.printf("Successfully deleted %d rows for PRODUCT_TYPE_ATTRIBUTES table\n", rows3);
			System.out.printf("Successfully deleted %d rows for PRODUCT_TYPE_ATTRIBUTE_VALUES table\n", rows4);
			System.out.printf("Successfully deleted %d rows for CATEGORIES table\n", rows5);
			System.out.printf("Successfully deleted %d rows for PRODUCT_CATEGORIES table\n", rows6);
			
			//System.exit(1);
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}				
			}
		}
	}
	
	private static final String USER = "GOLS051";
	private static final String PWD = "longaz";
}
